package com.erpoa.mapper;

import com.alibaba.fastjson.JSONObject;
import com.erpoa.entity.*;
import org.apache.ibatis.annotations.*;

import java.sql.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @description:
 * @author: jamin.chen
 * @createDate: 2020/1/7 10:42
 * @version: 1.0
 */
@Mapper

public interface CarDataMapper {
    /**
     *
     * @return
     */
    List<CarData> queryCarDataList(@Param("carNo") String carNo, @Param("trayNo") String trayNo);


    /**
     *
     * @return
     */
    //@Select(" select count(*) from Wms_Review_tray where CAR_NO like concat('%',#{carNo},'%')   AND TRAY_NO like concat('%',#{trayNo},'%')  ")
    Long getTotal(@Param("carNo") String carNo, @Param("trayNo") String trayNo);


    /**
     *
     * @param params
     * @return
     */
    int updTrayData(@Param("params") Map params);


    /**
     *
     * @param carNo
     * @param trayNo
     * @return
     */
   // @Select(" select count(*) from Wms_Review_tray  where CAR_NO like concat('%',#{carNo},'%')   AND TRAY_NO like concat('%',#{trayNo},'%')  ")
    int countTrayData1(@Param("carNo") String carNo, @Param("trayNo") String trayNo);

    LogicBean selectBaseLogicBeanByMBarcode(@Param("mBarcode")String mBarcode);

    ProdPack selectprodPackQueryByParam(@Param("mPckBarCode") String mPckBarCode,@Param("prodNo") String prodNo, @Param("orgNo") String orgNo);


    LogicBean selectHasDetailSqlBybarCode(@Param("mBarcode")String mBarcode);

    ReviewLine selectSqlLineBybarCode(@Param("logicBean") LogicBean logicBean);

    LogicBean selectHasListNoByLoginBean(@Param("logicBean") LogicBean logicBean, @Param("defOutDate") String defOutDate);

    LogicBean selectReviewIdSql1(@Param("logicBean")LogicBean logicBean,@Param("listNo") String listNo, @Param("defOutDate") String defOutDate);

    int insertData(@Param("logicBean") LogicBean logicBean,@Param("user") MgUser user);

    String selectTraySqlCount(@Param("sName") String sName, @Param("userCode") String userCode, @Param("sdfdate") String sdfdate);

    LogicBean selecthasListNoCount(@Param("logicBean") LogicBean logicBean ,@Param("defOutDate") String defOutDate);

    LogicBean selectreviewIdSqlSname(@Param("sName")String  sName,@Param("listNo") String listNo, @Param("defOutDate")String defOutDate);

    ReviewAll selectsqlAllQuery(@Param("logicBean") LogicBean logicBean);

    int updatesqlUpAll(@Param("logicBean") LogicBean logicBean,@Param("prodSpec") String prodSpec);

    int insertsqlDetail(@Param("logicBean") LogicBean logicBean, @Param("mBarcode") String mBarcode, @Param("trayNo") String trayNo, @Param("userCode") String userCode, @Param("nowDate") Date nowDate,
                        @Param("prodSpec") String prodSpec, @Param("reviewLine") ReviewLine reviewLine);

    int insertparamtray(@Param("logicBean") LogicBean logicBean, @Param("trayNo") String trayNo,@Param("prodSpec") String prodSpec,
                        @Param("userCode") String userCode);

    int selectCountsqlTrayDesc(@Param("logicBean") LogicBean logicBean);

    int selectsqlreviewTrayDesc(@Param("logicBean")LogicBean logicBean);

    ReviewAll selectsqlReviewAllQuery(@Param("logicBean")LogicBean logicBean);

    List<Map> requestDeliveryDetail(@Param("barcode") String barcode);

    @Select("select * from (select TRAY_NUMBER trayNumber,REVIEW_ID reviewId  from WMS_REVIEW_DETAIL where TRAY_NUMBER=#{trayNumber} " +
            "    order by scan_date desc) where  rownum = 1")
    LogicBean selectreviewSql(@Param("trayNumber") String trayNumber);

    @Select("  select count(list_no) num, REVIEW_ID reviewId"+
            "  from wms_review_all where " +
            "   prod_no = #{logicBean.prodNo} and list_no= #{logicBean.listNo} and org_no = #{logicBean.orgNo} and DFT_OUT_DATE = to_date(#{defOutDate},'yyyy-mm-dd') " +
            "   group by list_no ,REVIEW_ID " )
    LogicBean selecthasListNoCountsecond(@Param("logicBean") LogicBean logicBean, @Param("defOutDate") String defOutDate);

    @Select("  select count(tray_no)  from wms_review_tray  " +
            " where TRAY_NO = #{logicBean.trayNumber} and status = '2' ")
    int selectTraySqlStatus(@Param("logicBean") LogicBean logicBean);

    @Select( "select sum(SCAN_QTY) scanQty ,sum(ALL_QTY) allQty,sum(ALL_QTY_DESC) allQtyDesc from wms_review_all " +
            " where REVIEW_ID = #{logicBean.reviewId}   ")
    ReviewAll selectsqlAllQuerySecond(@Param("logicBean") LogicBean logicBean);

    @Update(" update WMS_REVIEW_ALL set SCAN_QTY = SCAN_QTY + #{ logicBean.logisticQty}  , SCAN_QTY_DESC = SCAN_QTY_DESC + #{ prodSpec } " +
            " where PROD_NO =#{logicBean.prodNo} and list_no = #{logicBean.listNo} and org_no = #{ logicBean.orgNo} ")
    int updatesqlUpAllSecond(@Param("logicBean")LogicBean logicBean,@Param("prodSpec") String prodSpec);

    @Update(" update WMS_REVIEW_TRAY set SCAN_QTY = SCAN_QTY+ #{logicBean.logisticQty} , SCAN_BOX = SCAN_BOX+ #{prodSpec}" +

            " where TRAY_NO =#{trayNumber} ")
    int updatesqlUptraySecond(@Param("logicBean")LogicBean logicBean, @Param("prodSpec")String prodSpec, @Param("trayNumber")String trayNumber);


    @Insert( "INSERT INTO WMS_REVIEW_DETAIL (REVIEW_ID,LOGISTIC_NO,LIST_NO,TRAY_NUMBER,TRAY_QTY," +
            "SCANNERS,DEF_OUT_DATE,DETAIL_BOX,PROD_NO,PROD_NAME,PROD_SPEC,ORG_NO,CUST_NAME,LINE_ID)VALUES" +
            "(#{logicBean.reviewId},#{mBarcode},#{logicBean.listNo},#{trayNumber},#{logicBean.logisticQty},#{userCode},null,#{prodSpec},#{logicBean.prodNo},#{ logicBean.prodName}," +
            "#{reviewLine.prodSpec},#{logicBean.orgNo},#{logicBean.sName},#{ logicBean.lineID})" )
       Integer insertsqlDetailSecond(@Param("logicBean")LogicBean logicBean, @Param("mBarcode")String mBarcode,@Param("trayNumber") String trayNumber,
                                  @Param("userCode")String userCode, @Param("nowDate")Date nowDate,
                                  @Param("prodSpec")String prodSpec, @Param("reviewLine")ReviewLine reviewLine);

    /*  @Select("  select count(*) scanBox from wms_review_detail " +
              "where  TRAY_NUMBER = #{trayNumber}  ")*/
    @Select("   select count(*) scanBox  from wms_review_detail " +
            "                    where  TRAY_NUMBER = #{ltrayNumber} and status ='Y' ")
    ReviewTray selectSqlTrayDesc(@Param("trayNumber") String trayNumber);

    @Select("  select count(*) scanBox from wms_review_detail " +
            "where  review_id = #{reviewId} and status = 'Y'")
    ReviewTray selectSqlTrayDescAll(@Param("reviewId") String reviewId);

    @Select("  select  REVIEW_ID reviewId"+
            "  from wms_review_all where " +
            "   CUST_NAME= #{sName} and instr(list_no, #{listNo}) > 0 and DFT_OUT_DATE = to_date(#{defOutDate},'yyyy-mm-dd') and rownum=1 order by DFT_OUT_DATE desc ")
    LogicBean selectreviewIdSql(@Param("sName") String sName, @Param("listNo") String listNo, @Param("defOutDate") String defOutDate);

    @Update(" update WMS_REVIEW_TRAY set STATUS = '2',cust_name =#{sname}" +

            " where TRAY_NO =#{trayNumber} ")
    int updatesqlUptrayConfirm(@Param("trayNumber") String trayNumber, @Param("sname") String sname);

    @Select(" select TRAY_NO trayNo,SCAN_QTY scanQty ,SCAN_BOX scanBox,REVIEW_ID reviewId from WMS_REVIEW_TRAY  "+
            "where TRAY_NO = #{trayNumber} and STATUS ='2' " )
    ReviewTray trayQuerydel(@Param("trayNumber") String trayNumber);

    @Select("SELECT  TRAY_NUMBER trayNumber, TRAY_QTY trayQty,DETAIL_BOX detailBox,PROD_NO prodNo" +
            "   from Wms_Review_detail where tray_number= #{trayNumber} and REVIEW_ID = #{reviewId} ")
    List<ReviewDetail> selectReviewDetailList(@Param("trayNumber")String trayNumber, @Param("reviewId")String reviewId);

    @Update(" update WMS_REVIEW_ALL set SCAN_QTY = SCAN_QTY - #{ reviewDetail.trayQty }  , " +
            "SCAN_QTY_DESC = SCAN_QTY_DESC  - #{ reviewDetail.detailBox} " +
            " where PROD_NO = #{reviewTray.trayNo} and REVIEW_ID = #{reviewTray.reviewId} ")
    Integer updatesqlUpAllDetailSecond(@Param("reviewDetail") ReviewDetail reviewDetail, @Param("reviewTray") ReviewTray reviewTray);

    @Delete(" delete WMS_REVIEW_DETAIL " +
            " where TRAY_NUMBER = #{trayNumber} ")
    int delsqlDelDetail(@Param("trayNumber") String trayNumber);

    @Delete(" delete WMS_REVIEW_TRAY  "+
            " where TRAY_NO =#{trayNumber} and STATUS ='2' ")
    int delsqlDelTray(@Param("trayNumber") String trayNumber);

    @Select("select count (PCK_BARCODE) from lms_prod_pack_modle where PCK_BARCODE =#{mPckBarcode} ")
    int selectsqllppmcheckPckBarCode(@Param("mPckBarcode") String mPckBarcode);

    @Select("select max(tray_no)  from wms_review_tray where " +
            "   update_date = (select max(update_date) from wms_review_tray where TO_CHAR(update_date,'YYYY-MM-dd')=TO_CHAR(SYSDATE,'YYYY-MM-dd')) ")
    int selectTraySqlCountantoCreateWithSurplus();

    @Insert("INSERT INTO wms_review_tray (REVIEW_ID,TRAY_NO,SCAN_QTY,SCAN_BOX,STATUS,CREATE_DATE,CREATE_BY,UPDATE_DATE,UPDATE_BY" +
            ",CUST_NAME)  VALUES(#{reviewId},#{trayNo},#{surplusQty},#{scanBox},#{status},sysdate,#{userCode},sysdate,#{userCode1},#{sName})")
    int insertsqlTrayantoCreateWithSurplus(@Param("reviewId") Object reviewId, @Param("trayNo") String trayNo, @Param("surplusQty") Integer surplusQty,
                                               @Param("scanBox") String scanBox, @Param("status") String status,
                                               @Param("userCode") String userCode,   @Param("userCode1") String userCode1,
                                               @Param("sName") Object sName);


    @Insert("INSERT INTO WMS_REVIEW_DETAIL " +
            "(REVIEW_ID,LOGISTIC_NO,LIST_NO,TRAY_NUMBER,TRAY_QTY,SCAN_DATE,SCANNERS,DEF_OUT_DATE,DETAIL_BOX,PROD_NO,PROD_NAME," +
            "PROD_SPEC,ORG_NO,CUST_NAME)VALUES(#{reviewId},#{autoCreate},#{autoCreate1},#{trayNo},#{surplusQty},sysdate,#{userCode},sysdate,#{s},#{prodNo},#{prodName},#{s1},#{s2},#{sName})")
    int insertsqlDetailantoCreateWithSurplus(@Param("reviewId") Object reviewId, @Param("autoCreate") String autoCreate,
                                                 @Param("autoCreate1") String autoCreate1, @Param("trayNo") String trayNo,
                                                 @Param("surplusQty") Integer surplusQty,
                                                 @Param("userCode") String userCode,
                                                 @Param("s") String s, @Param("prodNo") Object prodNo, @Param("prodName") Object prodName,
                                                 @Param("s1") String s1, @Param("s2") String s2, @Param("sName") Object sName);


    @Update(" update WMS_REVIEW_ALL set SCAN_QTY = SCAN_QTY + #{surplusQty} , SCAN_QTY_DESC = SCAN_QTY_DESC  +  1 " + //默认一箱
            "   where PROD_NO =#{prodNo} and review_id = #{reviewId}")
    int updatesqlUpAllantoCreateWithSurplus(@Param("surplusQty") Integer surplusQty, @Param("scanqtydesc") Integer scanqtydesc,
                                                @Param("prodNo") Object prodNo, @Param("reviewId") Object reviewId);

    @Select(" select prod_name||'/'||(all_qty-scan_qty)  from wms_review_all " +
            "where REVIEW_ID= (select  review_id  from wms_review_tray where tray_no=  #{trayNumber}) " +
            "and all_qty-scan_qty >0  ")
    List<JSONObject> selectgetComfirmLcl(@Param("trayNumber") String trayNumber);

    @Select(" select TRAY_NO trayNo,SCAN_QTY scanQty ,SCAN_BOX scanBox,REVIEW_ID reviewId,CUST_NAME custName from WMS_REVIEW_TRAY  "+
            "where TRAY_NO = #{trayNumber}  ")
    ReviewTray selecttrayQuerysubmitComfirmLcl(@Param("trayNumber")String trayNumber);



     @Update(" update WMS_REVIEW_ALL set SCAN_QTY = SCAN_QTY + #{logisticQty} ,SCAN_QTY_DESC = SCAN_QTY_DESC + #{scan_qty_desc} "+
            " where PROD_NAME = #{prodName} and REVIEW_ID = #{reviewId} ")
     int updatesqlUpAllsubmitComfirmLcl(@Param("logisticQty") String logisticQty, @Param("scan_qty_desc") double scan_qty_desc,
                                            @Param("prodName") String prodName, @Param("reviewId") String reviewId);
    @Select(" select prod_no prodNo,org_no orgNo  from WMS_REVIEW_ALL where PROD_NAME = #{prodName} and REVIEW_ID = #{reviewId}   ")
    LogicBean selectgetAllResult(@Param("prodName") String prodName, @Param("reviewId") String reviewId);

    @Update(" update WMS_REVIEW_TRAY set SCAN_QTY = SCAN_QTY+ #{logisticQty} , SCAN_BOX = SCAN_BOX+#{scan_qty_desc}"+
            " where TRAY_NO =#{trayNumber}")
    int updatesqlUpsubmitComfirmLcl(@Param("logisticQty") Integer logisticQty, @Param("scan_qty_desc") double scan_qty_desc, @Param("trayNumber") String trayNumber);

    @Insert("INSERT INTO WMS_REVIEW_DETAIL (REVIEW_ID,LOGISTIC_NO,LIST_NO,TRAY_NUMBER,TRAY_QTY," +
            "SCANNERS,DEF_OUT_DATE,DETAIL_BOX,PROD_NO,PROD_NAME,PROD_SPEC,ORG_NO,CUST_NAME,LINE_ID,PROD_MERGE)" +
            "VALUES(#{reviewId},#{s},#{s1},#{trayNumber},#{toString},#{userCode},#{o},#{scan_qty_desc},#{toString1},#{toString2},#{i},#{orgNo},#{toString3}," +
            "#{s2},#{s45})")
    int insertsqlDetailsubmitComfirmLcl(@Param("reviewId") String reviewId, @Param("s'") String s, @Param("s1") String s1,
                                            @Param("trayNumber") String trayNumber, @Param("toString") String toString,
                                            @Param("userCode") String userCode, @Param("o") Object o, @Param("scan_qty_desc") double scan_qty_desc,
                                            @Param("toString1") String toString1, @Param("toString2") String toString2, @Param("i") int i,
                                            @Param("orgNo") String orgNo, @Param("toString3") String toString3, @Param("s2") String s2, @Param("s45") String s45);

    @Select("  select * from (select  REVIEW_ID reviewId"+
            "  from wms_review_all where " +
            "   CUST_NAME= #{sName}  and DFT_OUT_DATE = to_date(#{defOutDate},'yyyy-mm-dd') order by DFT_OUT_DATE desc )   where rownum=1  ")
    LogicBean selectreviewIdSqlupdateDataByCustName(@Param("sName") String sName, @Param("defOutDate") String defOutDate);

    @Select("select * from (select wlc.pick_list_no pickListNo, wlc.list_no listNo,wlc.org_no orgNo,lpd.prod_no prodNo, lpd.prod_name prodName," +
            " wlc.qty logisticQty ,loc.sName sName,loc.cust_no custNo,wlt.DFT_OUT_DATE defOutDate,wlt.status_ctr statisCtr,wlc.line_id lineId" +
            " from wms_out_logistic  wlc left join wms_out_list wlt on wlc.list_no=wlt.list_no " +
            " left join LMS_ORG_CUST loc on loc.cust_no = wlt.cust_no  left join LMS_PROD lpd on lpd.prod_no = wlc.prod_no  "
            + " where wlc.logistic_no=#{mBarcode}   ORDER BY DEFOUTDATE DESC) where  ROWNUM=1")
    LogicBean selectpckConfirm(String mBarcode);


    @Select(" select * from (select PCK_BARCODE pckBarcode ,PROD_NO prodNo ,PCK_QTY pckQty,PCK_UN pckUn from lms_prod_pack_modle "+
            " where  PROD_NO = #{prodNo} and org_no = #{orgNo}  and PCK_BARCODE >0   order by  PCK_QTY desc) where rownum=1")
    ProdPack selectprodPackpckConfirm(@Param("prodNo") String prodNo, @Param("orgNo") String orgNo);


    @Select("   select  distinct wrt.tray_no  trayNumber "+
            " from wms_review_tray wrt  left join wms_review_all wra  on wra.review_id = wrt.review_id where wrt.status='1' "+
            " and wra.CUST_NAME= #{sName}   and ( wra.DFT_OUT_DATE = to_date('2020-10-12', 'yyyy-mm-dd') " +
            "      and trunc(wra.DFT_OUT_DATE)  = trunc(sysdate))and rownum=1 order by DFT_OUT_DATE desc ")
    LogicBean selecttrayConfirmByCustName(@Param("sName") String sName, @Param("defOutDate") String defOutDate);



    int insertDataDetail(@Param("logicBean") LogicBean logicBean, @Param("user") MgUser user);

    int updateSqlDetail(@Param("logicBean") LogicBean logicBean,@Param("mBarcode") String mBarcode,@Param("trayNo") String trayNo,@Param("user") MgUser user);


    int updatesqlDetailSecond(@Param("logicBean") LogicBean logicBean, @Param("mBarcode") String mBarcode, @Param("trayNumber") String trayNumber, @Param("user") MgUser user);


    @Select("select prod_name prodName,area_no||'  '||count(1)||'件'  areaNo, wrd.list_no||'('||wrd.ORG_NO||')'  orgNo  from wms_review_detail wrd " +
            "left join LMS_PROD lpd on lpd.prod_no = wrd.prod_no  " +
            " where cust_name = #{sName} and  trunc(def_out_date) = trunc(to_date(#{defOutDate},'yyyy/MM/dd')) and status ='N' " +
            " group by prod_name  ,area_no  ,wrd.ORG_NO ,wrd.list_no " +
            " order by area_no  ,wrd.list_no desc")
    List<Map> descGet(@Param("sName") String sName, @Param("defOutDate") String defOutDate);

    // @Select(" select distinct NVL(SNAME,'HJ')   ,NVL(CUST_NO,'HJ')   from LMS_ORG_CUST  ")
    List<Map> selectSname(@Param("sName") String sName, @Param("time") String time);

    /**
     * @param sName
     * @param time
     * @return
     */
    List<Map> selectTray(@Param("sName") String sName, @Param("time") String time, @Param("sCode") String sCode);

    @Select(" select count(1) from wms_review_detail where LOGISTIC_NO = #{mBarcode} and status ='Y' ")
    int countFuHeBybarCode(@Param("mBarcode") String mBarcode);

    @Select("   select count(*)   from wms_review_detail " +
            "   where  cust_name =  #{logicBean.sName}  and (trunc(INSERT_DATE)  = trunc(sysdate) " +
            "   or  trunc(def_out_date) = trunc(to_date(#{logicBean.defOutDate},'yyyy/MM/dd')) ) ")
    Integer countReviewAllQuery(@Param("logicBean") LogicBean logicBean);

    @Select(" with o_list as " +
            "         ( " +
            "         select lt.org_no,lt.list_no,ct.cust_no,ct.sname ,lt.dft_out_date " +
            "         from wms_out_list lt " +
            "         join  LMS_ORG_CUST ct on lt.org_no=ct.org_no and lt.cust_no=ct.cust_no and ct.sname=#{sname} " +
            "         where" +
            "          lt.dft_out_date =  to_date(#{defOutDate},'yyyy-MM-dd') " +
            "         ),o_line as ( " +
            "         select le.org_no,le.list_no,le.prod_no,le.prod_name,le.prod_spec,nvl(sum(le.qty),0) as sumqty, " +
            "         nvl((select max(pck_qty) from lms_prod_pack_modle lm where  le.prod_no=lm.prod_no),0) pck_qty " +
            "         from wms_out_line le " +
            "         where   le.create_date>add_months(sysdate,-3) " +
            "         group by le.list_no,le.prod_no,le.prod_name,le.prod_spec " +
            "         ) " +
            "         select    ole.prod_name PRODNAME,olt.list_no ||'('|| olt.org_no ||')' AREANO,round(ole.sumqty/ole.pck_qty,2)||'件' ORGNO " +
            // "         select    ole.prod_name PRODNAME,olt.list_no  AREANO,round(ole.sumqty/ole.pck_qty,2)||'件' ORGNO " +
            "         from o_list olt " +
            "         join o_line ole on olt.org_no=ole.org_no and olt.list_no=ole.list_no  order by olt.list_no")
    List<HashMap> countAllDescBySname(@Param("sname") String sname, @Param("defOutDate") String defOutDate);

    @Select("  SELECT * FROM (select PCK_BARCODE pckBarcode ,PROD_NO prodNo ,PCK_QTY pckQty,PCK_UN pckUn from lms_prod_pack_modle " +
            "                     where instr(PCK_BARCODE , #{mPckBarCode})>0 AND PROD_NO = #{prodNo} " +
            "                      and ORG_NO = #{orgNo}  and PCK_BARCODE >0 order by  PCK_QTY desc) WHERE   rownum=1  ")
    ProdPack selectprodPackQueryByParamlike(@Param("mPckBarCode") String mPckBarCode, @Param("prodNo") String prodNo, @Param("orgNo") String orgNo);


    @Select("select count(1) from wms_review_detail where tray_number =#{traynumber} and status = 'Y'")
    String counthasscanDEtail(@Param("traynumber") String traynumber);


    @Select("  select count(*)   from wms_review_detail " +
            "                     where  cust_name in (select cust_name from wms_review_detail where tray_number = #{traynumber})  " +
            "                      and trunc(scan_date)  = trunc(sysdate)  and status ='Y' ")
    String counthasscanDEtailAll(@Param("traynumber") String traynumber);

    @Select("  select LOGISTIC_NO ORGNO,prod_no AREANO,USER_NAME||'('||to_char(scan_date,'HH24:mi:ss')||')' PRODNAME  from WMS_REVIEW_DETAIL " +
            "where instr(tray_number ,#{traynumber})>0 order by scan_date desc")
    List<HashMap> selectTrayDetailByTrayNo(@Param("traynumber") String traynumber);

    @Select(" select cust_name from wms_review_detail where LOGISTIC_NO =#{barcode} ")
    String selectSameByBarcode(@Param("barcode") String barcode);

    @Select("   select max(tray_no)  from wms_review_tray where " +
            "   trunc(update_date)  = trunc(sysdate)  and update_by = #{userCode}  and instr(tray_no,#{sdfdate})>0 ")
   /* @Select("   select max(tray_number)  from wms_review_detail ,wms_review_tray  where tray_number=tray_no and " +
            "             trunc(scan_date)  = trunc(sysdate)  and scanners = #{userCode} and instr(tray_number,#{sdfdate})>0   ")*/
    String selectTraySqlCountre(@Param("userCode") String userCode, @Param("sdfdate") String sdfdate);

    @Insert(" INSERT INTO wms_review_tray (REVIEW_ID,TRAY_NO,SCAN_QTY,SCAN_BOX,STATUS,CREATE_DATE,CREATE_BY, " +
            "                        UPDATE_DATE,UPDATE_BY,CUST_NAME) " +
            "                        VALUES('104554',#{trayNo},'45','54', " +
            "                        '1',sysdate,#{userCode},sysdate,#{userCode},'10104554') ")
    int createTrayNumber(@Param("trayNo") String trayNo, @Param("userCode") String userCode);

    @Select("  select TRAY_NUMBER TRAYNUMBER from wms_review_detail where LOGISTIC_NO = #{barcode} ")
    String countFuHeBybarCodetn(@Param("barcode") String barcode);

    @Select("   select list_no listNo,org_no orgNo,TRAY_NUMBER trayNumber,prod_no prodNo,TRAY_QTY logisticQty,cust_name sName, scanners," +
            "      to_char( def_out_date,'yyyy-MM-dd') defOutDate from  wms_review_detail where LOGISTIC_NO =#{barcode} ")
    LogicBean selectBaseLogicBeanByMBarcodesecond(@Param("barcode") String barcode);

    @Select("   select tray_number TRAYNUMBER,wms_review_detail.cust_name CUSTNAME from      wms_review_tray left join wms_review_detail " +
            "on tray_number=tray_no  where wms_review_tray.status =1 and scan_date =\n" +
            "   (select max(scan_date) from wms_review_detail where scanners=#{userCode}) " +
            " and instr( tray_number,#{userCode} )>0   ")
    HashMap selectdetailSqlCByCode(@Param("userCode") String userCode);

    @Select(" select distinct cust_name from wms_review_detail where TRAY_NUMBER =#{trayNumber} ")
    String selectSnameBy(@Param("trayNumber") String trayNumber);

    @Select("    SELECT status FROM WMS_REVIEW_tray  WHERE  tray_no=#{trayNumber} ")
    String selecttraystatus(@Param("trayNumber") String trayNumber);

    @Select("  select count(*)   from wms_review_detail " +
            "                     where  cust_name =  #{sName}  and trunc(scan_date)  = trunc(sysdate)  and status ='Y' ")
    Integer selectsqlreviewsName(@Param("sName") String sName);

    @Select(" with o_list as " +
            "         ( " +
            "         select lt.org_no,lt.list_no,ct.cust_no,ct.sname ,lt.dft_out_date " +
            "         from wms_out_list lt " +
            "         join  LMS_ORG_CUST ct on lt.org_no=ct.org_no and lt.cust_no=ct.cust_no and ct.sname=#{sname} " +
            "         where" +
            "          lt.dft_out_date =  to_date(#{defOutDate},'yyyy-MM-dd') " +
            "         ),o_line as ( " +
            "         select le.org_no,le.list_no,le.prod_no,le.prod_name,le.prod_spec,nvl(sum(le.qty),0) as sumqty, " +
            "         nvl((select max(pck_qty) from lms_prod_pack_modle lm where le.org_no=lm.org_no and le.prod_no=lm.prod_no),0) pck_qty " +
            "         from wms_out_line le " +
            "         where   le.create_date>add_months(sysdate,-3) " +
            "         group by le.org_no,le.list_no,le.prod_no,le.prod_name,le.prod_spec " +
            "         ) " +
            "         select      sum( round(ole.sumqty/ole.pck_qty,2))   " +
            "         from o_list olt " +
            "         join o_line ole on olt.org_no=ole.org_no and olt.list_no=ole.list_no  order by olt.list_no")
    String countReviewAllQuerysname(@Param("sname") String sname, @Param("defOutDate") String defOutDate);

    @Select(" select defoutdate  from (select  to_char( def_out_date,'yyyy-MM-dd') defoutdate from wms_review_detail where  " +
            "CUST_NAME =#{sName} and trunc(init_date)  = trunc(sysdate) order by init_date desc) where rownum=1")
    String selectLastDateBySname(@Param("sName") String sName);

    @Select(" select  distinct cust_name  from wms_review_detail where tray_number=#{trayNumber} ")
    String selectTraySName(@Param("trayNumber")String trayNumber);

    @Select(" select count(1) from wms_review_tray where tray_no=#{trayNumber}")
    int selectTno(@Param("trayNumber")String trayNumber);

    @Select(" select count(1) from wms_review_detail where TRAY_NUMBER =#{trayNo} ")
    int counttrayExist(@Param("trayNo") String trayNo);

    @Select("   select LOGISTIC_NO logisticNo,list_no listNo,org_no orgNo,TRAY_NUMBER trayNumber,prod_no prodNo,TRAY_QTY logisticQty,cust_name sName, " +
            // "TODO cjm 上线后修改      to_char( def_out_date,'yyyy-MM-dd') defOutDate from  wms_review_detail where  sysdate-5/(24*60) <= init_date  ")
            "      to_char( def_out_date,'yyyy-MM-dd') defOutDate from  wms_review_detail where  sysdate-10/(24*60) <= init_date  ")

    List<LogicBean> selectBaseLogicBeanSByMBarcodesecond();

    @Select(" select tray_number trayNumber,status,LOGISTIC_NO logisticNo from wms_review_detail where  LOGISTIC_NO = #{logicBean.logisticNo}  ")
    LogicBean checkTrayNoStatus(@Param("logicBean") LogicBean logicBean);

    //19打印确认事后补偿45
    @Update("   update  WMS_REVIEW_DETAIL set CHECKED = TRAY_QTY ,UNCHECKED= 0,STATUS ='Y',OUT_DATE=sysdate,TRAY_NUMBER=#{trayNumber}, " +
            "      PROD_MERGE='45',  SCAN_DATE =sysdate , SCANNERS = #{scanners} where LOGISTIC_NO = #{logisticNo} and STATUS ='N' ")
    int updateSqlDetailredis(@Param("trayNumber") String trayNumber, @Param("logisticNo") String logisticNo, @Param("scanners") String scanners);

    @Select(" select nvl(prod_name,'无') PRODNAME, nvl(spec,'无') SPEC  from LMS_PROD" +
            "  where prod_no=#{prodNo} and org_no=#{orgNo}  ")
    HashMap selectProdlms(@Param("prodNo") String prodNo, @Param("orgNo") String orgNo);

    //定时批量事后补偿54
    @Update("   update  WMS_REVIEW_DETAIL set CHECKED = TRAY_QTY ,UNCHECKED= 0,STATUS ='Y',TRAY_NUMBER=#{trayNumber},PROD_MERGE='54', " +
            "        SCANNERS = #{scanners} where LOGISTIC_NO = #{logisticNo} and STATUS ='N'  ")
    int updateSqlDetailredistime(@Param("trayNumber") String trayNumber, @Param("logisticNo") String logisticNo, @Param("scanners") String scanners);
}
